package SQL练习

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.expressions.Window
import org.junit

class Test {
  val spark = SparkSession.builder()
    .master("local[6]")
    .appName("Test")
    .getOrCreate()

  import spark.implicits._
  import org.apache.spark.sql.functions._

  /**
    * 找出所有从不订购任何东西的客户。
    */
  @junit.Test
  def Test01(): Unit = {
    val cust = Seq(
      (1, "Joe"),
      (2, "Henry"),
      (3, "Sam"),
      (4, "Max")
    ).toDF("id", "name")
    val order = Seq(
      (1, 3),
      (2, 1)
    ).toDF("id", "cid")
    val result = cust.join(order, cust.col("id") === order.col("cid"), joinType = "leftanti")
      .select('name)
      .show()
  }

  /**
    * 找出每个部门工资最高的员工
    */
  @junit.Test
  def Test02(): Unit = {
    val emp = Seq(
      (1, "Joe", 70000, 1),
      (2, "Henry", 80000, 2),
      (3, "Sam", 60000, 2),
      (4, "Max", 90000, 1)
    ).toDF("id", "name", "sales", "deptid")
    val dept = Seq(
      (1, "IT"),
      (2, "Sales")
    ).toDF("deptid", "name")
    val result = emp.join(dept, emp.col("deptid") === dept.col("deptid"))
      .select(dept.col("name") as "dname", emp.col("name"), emp.col("sales"))
      .groupBy('dname)
      .agg(max('sales))
      .show()
  }

  /**
    * 找出每个部门工资前三高的员工
    */
  @junit.Test
  def Test03(): Unit = {
    val emp = Seq(
      (1, "Joe", 70000, 1),
      (2, "Henry", 80000, 2),
      (3, "Sam", 60000, 2),
      (4, "Max", 90000, 1),
      (5, "Janet", 69000, 1),
      (6, "Randy", 85000, 1)
    ).toDF("id", "name", "sales", "deptid")
    val dept = Seq(
      (1, "IT"),
      (2, "Sales")
    ).toDF("deptid", "name")
    val window = Window.partitionBy(dept.col("name"))
      .orderBy(emp.col("sales").desc)
    val result = emp.join(dept, emp.col("deptid") === dept.col("deptid"))
      .select(dept.col("name") as "dname", emp.col("name"), emp.col("sales"), dense_rank() over window as "rank")
      .where('rank <= 3)
      .show()
  }

  /**
    * 删除 Person 表中所有重复的电子邮箱，重复的邮箱里只保留 Id 最小 的那个
    */
  @junit.Test
  def Test04(): Unit = {
    val email = Seq(
      (1, "john@example.com"),
      (2, "bob@example.com"),
      (3, "john@example.com")
    ).toDF("id", "email")
    val result = email.groupBy('email)
      .agg(min('id) as "id")
      .show()
  }

  /**
    * 来查找与之前（昨天的）日期相比温度更高的所有日期的 Id
    */
  @junit.Test
  def Test05(): Unit = {
    val w = Seq(
      (1, "2015-01-01", 10),
      (2, "2015-01-02", 25),
      (3, "2015-01-03", 20),
      (4, "2015-01-04", 30)
    ).toDF("id", "date", "temp")
  }

  /**
    * 如果一个国家的面积超过300万平方公里，或者人口超过2500万，那么这个国家就是大国家。
    * 编写一个SQL查询，输出表中所有大国家的名称、人口和面积
    */
  @junit.Test
  def Test06(): Unit = {
    val coutries = Seq(
      ("Afghanistan", "Asia", 652230, 25500100, 20343000),
      ("Albania", "Europe", 28748, 22831741, 12960000),
      ("Algeria", "Africa", 2381741, 37100000, 188681000),
      ("Andorra", "Europe", 468, 78115, 3712000),
      ("Angola", "Africa", 1246700, 20609294, 100990000)
    ).toDF("name", "continent", "area", "population", "gdp")
    val result = coutries.select('name, 'population, 'area)
      .where('area >= 3000000 and 'population >= 25000000)
      .show()
  }

  /**
    * 请列出所有超过或等于5名学生的课
    */
  @junit.Test
  def Test07(): Unit = {
    val student = Seq(
      ("A", "Math"),
      ("B", "English"),
      ("C", "Math"),
      ("D", "Biology"),
      ("E", "Math"),
      ("F", "Computer"),
      ("G", "Math"),
      ("H", "Math"),
      ("I", "Math")
    ).toDF("sname", "class")
    student.groupBy('class)
      .agg(count('sname) as "count")
      .where('count >= 5)
      .select('class)
      .show()
  }

  /**
    * 找出所有影片描述为非 boring (不无聊) 的并且 id 为奇数 的影片，结果请按等级 rating 排列
    */
  @junit.Test
  def Test08(): Unit = {
    val Film = Seq(
      (1, "War", "great 3D", 8.9),
      (2, "Science", "fiction", 8.5),
      (3, "irish", "boring", 6.2),
      (4, "Ice song", "Fantacy", 8.6),
      (5, "House card", "Interesting", 9.1)
    ).toDF("id", "name", "desc", "rate")
    Film.where('desc =!= "boring" and 'id%2 =!=0)
      .orderBy('rate.desc)
      .show()
  }
}
